<?php

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 * Description of Metrics
 *
 * @author QuangKhoi
 */
class Metrics {

    public static function getTrendlineData($_filter) {
        if ($_filter["streamId"] == '' || $_filter["dateFrom"] == '' || $_filter["dateTo"] == '') {
            return;
        }
        $streamId = base64_decode($_filter["streamId"]);
        $language = $_filter["language"];
        $tagId = $_filter["tags"];
        $sentiment = 0;
        if (isset($_filter["sentiment"]))
            $sentiment = $_filter["sentiment"];
        $stream = Stream::withId($_filter["streamId"]);
        $type = $_filter["type"];
        $source = $_filter['source'];
        $sourceQuery = '';
        if ($source != array('0')) {
            $sourceUrl = implode('" | "', $source);
            $sourceQuery = ' @source ("' . $sourceUrl . '")';
        }
        $result = array();
        if ($tagId == array('0') && $sentiment == 0) {
            $user = User::getLogedInUser();
            if ($user->type == '10') {
                $df = new DateTime($_filter["dateFrom"]);
                $dt = new DateTime($_filter["dateTo"]);
                $userId = User::getCurrentUserId();
                $dateFrom = $df->format("Y-m-d");
                $dateTo = $dt->format("Y-m-d");
                $sentimentCols = " count(sentimentdata.messageid) as count, DATE_FORMAT(sentimentdata.datecreated, '%b %d %Y') as date";
                $table = "sentimentdata";
                $stream = "AND sentimentdata.streamid = $streamId";
                $date = "datecreated >= '$dateFrom 00:00:00' AND datecreated <= '$dateTo 23:59:59'";
                $group = "GROUP BY DATE_FORMAT(datecreated, '%b %d %Y')";
                $typeSQL = '';
                if ($type != array('0')) {
                    $typeId = implode(',', $type);
                    $typeSQL = " AND sentimentdata.type IN ($typeId)";
                }
                $db = Database::getInstance();
                $query = "SELECT $sentimentCols FROM $table WHERE $date $stream $typeSQL $group ORDER BY sentimentdata.datecreated";
                echo $query;
                $queryResult = $db->query($query);
                $query = "SELECT count(sentimentdata.threadid) FROM $table WHERE $date $score $stream $typeSQL GROUP BY sentimentdata.threadid";
//SUM -> posts
//Count -> threads
                $qresult = $db->query($query);
                $threadsCount = 0;
                $postsCount = 0;
                if ($qresult->returnCode == 200) {
                    if ($qresult->dataTable->numOfRow > 0) {
                        foreach ($qresult->dataTable->rows as $row) {
                            $postsCount += $row[0];
                            $threadsCount += 1;
                        }
                    }
                }
                $result["total"] = $threadsCount . ';' . $postsCount;
                $result["data"] = array();
                if ($queryResult->returnCode == 200) {
//create array;
                    if ($queryResult->dataTable->numOfRow > 0) {
                        foreach ($queryResult->dataTable->rows as $row) {
                            $result['data'][$row['date']] = 0 + $row['count'];
                        }
                    }
                }
            } else {
                $streamController = new StreamController($stream, new StreamView());
                $query = trim($stream->query);
                $spamQuery = SpamMessages::getQuery(base64_decode($_filter['streamId']));
                if ($type !== array('0')) {
                    $zTrendReader = new ZTrendReader($_filter["dateFrom"], $_filter["dateTo"], 7, '(' . $query . $spamQuery . ')' . $sourceQuery, $language, (array) $type);
                } else {
                    $zTrendReader = new ZTrendReader($_filter["dateFrom"], $_filter["dateTo"], 7, '(' . $query . $spamQuery . ')' . $sourceQuery, $language);
                }
                $zTrendCollection = new stdClass();
                try {
                    $zTrendCollection = $zTrendReader->read();
                } catch (Exception $e) {
                    $dates = Utils::getDatesFromRange($_filter["dateFrom"], $_filter["dateTo"]);
                    foreach ($dates as $date) {
                        if ($result['data'][$date->format('M d Y')] == null) {
                            $result['data'][$date->format('M d Y')] = 0;
                        }
                    }
                    $result["filter"] = $_filter;
                    return $result;
                }
                $result = array();
                $date = "";
                $data = "";
                $items = $zTrendCollection->getItems();

                $smt = Metrics::getTopSocialPlatformsData($_filter);
                $posts = array_sum($smt['threads']);
                unset($smt['threads']);
                unset($smt['authors']);
                $threads = array_sum($smt);
                $result["total"] = $posts . ';' . $threads;
                foreach ($items as $item) {
                    $date = $item->getDate()->format('M d Y');
                    $result['data'][$date] = $item->getTrend();
                }
            }
        } else {
            $df = new DateTime($_filter["dateFrom"]);
            $dt = new DateTime($_filter["dateTo"]);
            $userId = User::getCurrentUserId();
            $dateFrom = $df->format("Y-m-d");
            $dateTo = $dt->format("Y-m-d");
            $tagCols = '';
            $score = '';
            if ($sentiment > 0) {
                $sentimentCols = " count(sentimentdata.messageid) as count, DATE_FORMAT(sentimentdata.datecreated, '%b %d %Y') as date";
                $score = - ($sentiment - 2);
                $score = "AND score = $score";
                $table = "sentimentdata";
                $streamWhere = "AND sentimentdata.streamid = $streamId";
                $date = "datecreated >= '$dateFrom 00:00:00' AND datecreated <= '$dateTo 23:59:59'";
                $group = "GROUP BY DATE_FORMAT(datecreated, '%b %d %Y')";
            }
            if ($tagId != array('0')) {
                $sentimentCols = " count(signals.messageid) as count, DATE_FORMAT(sentimentdata.datecreated, '%b %d %Y') as date";
                $join = " LEFT JOIN signals ON sentimentdata.messageid = signals.messageid AND sentimentdata.streamid = signals.streamid LEFT JOIN tagsdata on tagsdata.signalid = signals.signalid ";
                $where = "";
                $tagIds = implode(',', $tagId);
                $tags = "AND tagsdata.tagid IN ($tagIds)";
                $table = "sentimentdata";
                $user = "AND tagsdata.userid = $userId";
                $streamWhere = "AND sentimentdata.streamid = $streamId";
                $date = "sentimentdata.datecreated >= '$dateFrom 00:00:00' AND sentimentdata.datecreated <= '$dateTo 23:59:59'";
                $group = "GROUP BY DATE_FORMAT( sentimentdata.datecreated,  '%b %d %Y' )";
            }
            $typeSQL = '';
            if ($type != array('0')) {
                $typeIds = implode(',', $type);
                $typeSQL = " AND sentimentdata.type IN ($typeIds)";
            }
            $sourceSQL = '';
            if ($_filter['source'] != array('0')) {
                $source = $_filter['source'];
                $sourceUrl = implode('","', $source);
                $sourceSQL = " AND sentimentdata.source = '$source'";
            }
            $db = Database::getInstance();
            $query = "SELECT $sentimentCols $tagCols FROM $table $join WHERE $date $score $streamWhere $tags $sourceSQL $typeSQL $group ORDER BY sentimentdata.datecreated";
            $queryResult = $db->query($query);
            $query = "SELECT count(sentimentdata.threadid) FROM $table $join WHERE $date $score $streamWhere $tags $sourceSQL $typeSQL GROUP BY sentimentdata.threadid";
//SUM -> posts
//Count -> threads
            $qresult = $db->query($query);
            $threadsCount = 0;
            $postsCount = 0;
            if ($qresult->returnCode == 200) {
                if ($qresult->dataTable->numOfRow > 0) {
                    foreach ($qresult->dataTable->rows as $row) {
                        $postsCount += $row[0];
                        $threadsCount += 1;
                    }
                }
            }
            $result["total"] = $threadsCount . ';' . $postsCount;
            $result["data"] = array();
            if ($queryResult->returnCode == 200) {
//create array;
                if ($queryResult->dataTable->numOfRow > 0) {
                    foreach ($queryResult->dataTable->rows as $row) {
                        $result['data'][$row['date']] = 0 + $row['count'];
                    }
                }
            }
        }
//append missing days
        $dates = Utils::getDatesFromRange($_filter["dateFrom"], $_filter["dateTo"]);
        if (count($result['data']) < count($dates)) {
            foreach ($dates as $date) {
                if (!isset($result['data'][$date->format('M d Y')])) {
                    $result['data'][$date->format('M d Y')] = 0;
                }
            }
        }
        $result["filter"] = $_filter;
        return $result;
    }

    public static function getSMTName($type) {
        switch ($type) {
            case 1:
                return "forum";
            case 2:
                return "news";
            case 3:
                return "blog";
            case 4:
                return "facebook";
            case 5:
                return "twitter";
            case 6:
                return "youtube";
            case 7:
                return "classifieds";
            case 8:
                return "google plus";
            case 9:
                return "retailers";
        }
    }

    public static function getTopSocialPlatformsData($_filter) {
//Do some stuff
        $language = $_filter["language"];
        $tagId = $_filter["tags"];
        $sentiment = 0;
        if (isset($_filter["sentiment"]))
            $sentiment = $_filter["sentiment"];
        $type = $_filter["type"];
        $source = $_filter['source'];
        $sourceQuery = '';
        $user = User::getLogedInUser();
        $data = array();
        if ($tagId == array('0') && $sentiment == 0) {
            if ($user->type == '10') {
                $df = new DateTime($_filter["dateFrom"]);
                $dt = new DateTime($_filter["dateTo"]);
                $sDateFrom = $df->format("Y-m-d");
                $sDateTo = $dt->add(new DateInterval("P1D"))->format("Y-m-d");
                $streamId = base64_decode($_filter['streamId']);
                $db = Database::getInstance();
                $typeSQL = '';
                if ($type != array('0')) {
                    $types = implode(',', $type);
                    $typeSQL = " AND sentimentdata.type IN ($types)";
                }
                $query = "SELECT count(sentimentdata.type) as post, type, datecreated FROM sentimentdata WHERE sentimentdata.streamid = $streamId AND datecreated >= '$sDateFrom 00:00:00' AND datecreated <= '$sDateTo 23:59:59' $typeSQL AND sentimentdata.type > 0 GROUP BY sentimentdata.type";
                $result = $db->query($query);
                if ($result->returnCode == 200) {
                    if ($result->dataTable->numOfRow > 0) {
                        foreach ($result->dataTable->rows as $row) {
                            $data[$row['type']] = (int) $row['post'];
                        }
                    }
                }
                return $data;
            } else {
                if ($source != array('0')) {
                    $sources = implode('" | "', $source);
                    $sourceQuery = ' @source ("' . urldecode($sources) . '")';
                }
                $stream = Stream::withId($_filter["streamId"]);
                $streamController = new StreamController($stream, new StreamView());
                $query = $stream->query;
                $spamQuery = SpamMessages::getQuery(base64_decode($_filter['streamId']));
                if ($type !== array('0'))
                    $zSmtReader = new ZSmtReader($_filter["dateFrom"], $_filter["dateTo"], 7, '(' . $query . $spamQuery . ')' . $sourceQuery, $language, $type);
                else
                    $zSmtReader = new ZSmtReader($_filter["dateFrom"], $_filter["dateTo"], 7, '(' . $query . $spamQuery . ')' . $sourceQuery, $language);
                $result = $zSmtReader->read();
                foreach ($result->getItems() as $dt) {
                    $data[$dt->getId()] = $dt->getNumberOfPosts();
                    $data['authors'][$dt->getId()] = $dt->getNumberOfAuthors();
                    $data['threads'][$dt->getId()] = $dt->getNumberOfThreads();
                }
                return $data;
            }
        } else {
            $df = new DateTime($_filter["dateFrom"]);
            $dt = new DateTime($_filter["dateTo"]);
            $sDateFrom = $df->format("Y-m-d");
            $sDateTo = $dt->add(new DateInterval("P1D"))->format("Y-m-d");
            $streamId = base64_decode($_filter['streamId']);
            $sentimentWhere = '';
            if ($sentiment > 0) {
                $sentiment = - ($sentiment - 2);
                $sentimentWhere = "AND sentimentdata.score = '$sentiment'";
            }
            $tagJoin = '';
            if ($tagId != array('0')) {
                $tagJoin = 'LEFT JOIN signals ON sentimentdata.messageid = signals.messageid AND sentimentdata.streamid = signals.streamid LEFT JOIN tagsdata ON tagsdata.signalid = signals.signalid LEFT JOIN tags ON tags.tagid = tagsdata.tagid';
                $tags = implode(',', $tagId);
                $tagWhere = "AND tagsdata.tagid IN ($tags)";
            }
            $sourceSQL = '';
            if ($_filter['source'] != array('0')) {
                $source = $_filter['source'];
                $sources = urldecode(implode('\',\'', $source));
                $sourceSQL = " AND sentimentdata.source IN ('$sources')";
            }
            $db = Database::getInstance();
            $typeSQL = '';
            if ($type != array('0')) {
                $types = implode(',', $type);
                $typeSQL = " AND sentimentdata.type IN ($types)";
            }
            $query = "SELECT count(sentimentdata.type) as post, type, datecreated FROM sentimentdata $tagJoin WHERE sentimentdata.streamid = $streamId AND datecreated >= '$sDateFrom 00:00:00' AND datecreated <= '$sDateTo 23:59:59' $typeSQL $tagWhere $sentimentWhere $sourceSQL AND sentimentdata.type > 0 GROUP BY sentimentdata.type";
            $result = $db->query($query);
            $data = array();
            if ($result->returnCode == 200) {
                if ($result->dataTable->numOfRow > 0) {
                    foreach ($result->dataTable->rows as $row) {
                        $data[$row['type']] = (int) $row['post'];
                    }
                }
            }
            return $data;
        }
    }

    /**
     * 
     * @param array $_filter
     * @return array of smt trend
     */
    public static function getSMTTrend($_filter) {
        $language = $_filter["language"];
        $tagId = $_filter["tags"];
        $sentiment = $_filter["sentiment"];
        $type = $_filter["type"];
        $source = $_filter['source'];
        $sourceQuery = '';
        $user = User::getLogedInUser();
        $data = array();
        if ($tagId == 0 && $sentiment == 0) {
            if ($user->type == '10') {
                for ($i = 1; $i <= 9; $i++) {
                    if ($type > 0) {
                        if (!is_array($type))
                            $type = array($type);
                        if (in_array($i, $type)) {
                            $_filter['type'] = $i;
                            $data[$i] = self::getTrendlineData($_filter);
                        }
                    } else {
                        $_filter['type'] = $i;
                        $data[$i] = self::getTrendlineData($_filter);
                    }
                }
                return $data;
            } else {
                if ($source != '0')
                    $sourceQuery = ' @source "' . urldecode($source) . '"';
                if ($tagId == 0 && $sentiment == 0) {
                    $stream = Stream::withId($_filter["streamId"]);
                    $streamController = new StreamController($stream, new StreamView());
                    $query = $stream->query;
                    $spamQuery = SpamMessages::getQuery(base64_decode($_filter['streamId']));
                    if ($type)
                        $zSmtReader = new ZTrendTypeReader($_filter["dateFrom"], $_filter["dateTo"], 7, '(' . $query . $spamQuery . ')' . $sourceQuery, $language, $type);
                    else
                        $zSmtReader = new ZTrendTypeReader($_filter["dateFrom"], $_filter["dateTo"], 7, '(' . $query . $spamQuery . ')' . $sourceQuery, $language);
                    $result = $zSmtReader->read();
                    foreach ($result as $key => $type) {
                        foreach ($type->getItems() as $dt) {
                            $date = $dt->getDate();
                            $data[$key][$date->format('d M Y')] = $dt->getTrend();
                        }
                    }
                }
                return $data;
            }
        } else {
            for ($i = 1; $i <= 9; $i++) {
                if ($type > 0) {
                    if (!is_array($type))
                        $type = array($type);
                    if (in_array($i, $type)) {
                        $_filter['type'] = $i;
                        $data[$i] = self::getTrendlineData($_filter);
                    }
                } else {
                    $_filter['type'] = $i;
                    $data[$i] = self::getTrendlineData($_filter);
                }
            }
            return $data;
        }
    }

    /* return array(
      'name' => 'Twitter;Blogs;Boards;News',
      'data' => '100071;5510;2548;1736',
      'trend' => '0,50000,80000,250000;590000,0,100000,250000;0,0,10000,250000;0,0,10000,250000'
      ); */

    public static function getSharingMetricsData($_filter) {
        $language = $_filter["language"];
        $tagsId = $_filter["tags"];
        $sentiment = $_filter["sentiment"];
        $streamId = base64_decode($_filter["streamId"]);
        $db = Database::getInstance();
        $dateFrom = new DateTime($_filter["dateFrom"]);
        $dateTo = new DateTime($_filter["dateTo"]);
        $sDateFrom = $dateFrom->format("Y-m-d");
        $sDateTo = $dateTo->format("Y-m-d");
        $type = $_filter["type"];
        if ($sentiment > 0) {
            $sentiment = - ($sentiment - 2);
            $sentimentWhere = "AND sentimentdata.score = '$sentiment'";
        }
        if ($tagsId > 0) {
            $tagWhere = "AND tagsdata.tagid = '$tagsId'";
        }
        $query = "SELECT sum(signals.sharecount), sum(signals.retweetcount) FROM sentimentdata LEFT JOIN signals on signals.messageid = sentimentdata.messageid LEFT JOIN tagsdata ON tagsdata.signalid = signals.signalid WHERE signals.streamid='$streamId' AND date >= '$sDateFrom' AND date <= '$sDateTo' $sentimentWhere $tagWhere";
        $result = $db->query($query);
        if ($result->returnCode == 200) {
            $row = $result->dataTable->rows[0];
            if ($row[0] != null)
                return array('name' => 'shares;retweets',
                    'data' => $row[0] . ';' . $row[1]);
        }
        return array('name' => 'shares;retweets',
            'data' => '0;0');
    }

    public static function getSentimentData($_filter) {
        $user = User::getLogedInUser();
        $userId = $user->id;
        if ($user->teamId == 2) {
            $userId = 33;
        }
        $trendLine = self::getTrendlineData($_filter);
        $language = $_filter["language"];
        $tagsId = $_filter["tags"];
        $sentiment = 0;
        if (isset($_filter["sentiment"]))
            $sentiment = $_filter["sentiment"];
        $streamId = base64_decode($_filter["streamId"]);
        $db = Database::getInstance();
        $dateFrom = new DateTime($_filter["dateFrom"]);
        $dateTo = new DateTime($_filter["dateTo"]);
        $sDateFrom = $dateFrom->format("Y-m-d");
        $type = $_filter["type"];
        $sDateTo = $dateTo->format("Y-m-d");
        $sentimentWhere = '';
        if ($sentiment > 0) {
            $sentiment = - ($sentiment - 2);
            $sentimentWhere = "AND sentimentdata.score = '$sentiment'";
        }
        $tagJoin = '';
        $tagWhere = '';
        if ($tagsId != array('0')) {
            $tags = implode(',', $tagsId);
            $tagJoin = 'LEFT JOIN signals ON sentimentdata.messageid = signals.messageid AND sentimentdata.streamid = signals.streamid LEFT JOIN tagsdata ON tagsdata.signalid = signals.signalid LEFT JOIN tags ON tags.tagid = tagsdata.tagid';
            $tagWhere = "AND tagsdata.tagid IN ($tags)";
        }
        $sourceSQL = '';
        if ($_filter['source'] != array('0')) {
            $source = $_filter['source'];
            $sources = implode('\',\'', $source);
            $sourceSQL = " AND sentimentdata.source IN ('$sources')";
        }
        $whereType = '';
        if ($type != array('0')) {
            $types = implode(',', $type);
            $whereType = "AND sentimentdata.type IN ($types)";
        }
        $query = "SELECT COUNT( * ) as count , score, DATE_FORMAT( datecreated,  '%b %d %Y' ) AS date, datecreated
                 FROM sentimentdata $tagJoin
                 WHERE sentimentdata.streamid = $streamId "
                . "AND sentimentdata.datecreated >= '$sDateFrom 00:00:00' "
                . "AND sentimentdata.datecreated <= '$sDateTo 23:59:59' "
                . "$sentimentWhere $tagWhere $whereType $sourceSQL 
                    GROUP BY DATE_FORMAT( datecreated,  '%b %d %Y' )  , score
                    ORDER BY datecreated ASC";
        echo $query;
        $result = $db->query($query)->dataTable->rows;
        $trendLineData = $trendLine['data'];
        $data = array();
        if (count($result) == 0) {
            foreach ($trendLineData as $key => $value) {
                $data[$key][-1] = 0;
                $data[$key][0] = 0;
                $data[$key][1] = 0;
                $data[$key][-2] = $value;
            }
        } else {
            foreach ($trendLine['data'] as $key => $value) {
                $data[$key][-1] = 0;
                $data[$key][0] = 0;
                $data[$key][1] = 0;
                $data[$key][-3] = 0;
                foreach ($result as $row) {
                    if ($row['date'] == $key)
                        $data[$row['date']][$row['score']] = 0 + $row['count'];
                    if ($user->teamId == 3 && $user->type !== 1 && $row['score'] == '-2')
                        $data[$row['date']][-2] = 0;
                }
                $date = new DateTime($key);
                if ($date < new DateTime('2014-04-01') && $streamId == 162) {
                    $data[$key][-2] = 0;
                } else {
                    if ($user->teamId == 3 && $user->type !== 1)
                        $data[$key][-2] = 0;
                    else
                    if (array_sum($data[$key]) < $value) {
                        $data[$key][-2] = $value - array_sum($data[$key]);
                    }
                }
            }
        }
//process data
        $result = array();
        $result['weekly'] = array();
        $result['monthly'] = array();
        $result['daily'] = array();
        foreach ($data as $key => $value) {
//daily
            $result['daily'][$key] = $value;

//weekly
            $date = new DateTime($key);
            $week = self::x_week_range($key);
            if (new DateTime($week[0]) < $dateFrom)
                $week[0] = $dateFrom->format('M d');
            if (new DateTime($week[1]) > $dateTo)
                $week[1] = $dateTo->format('M d');
            $dateStr = $week[0] . ' - ' . $week[1];
            foreach ($value as $k => $v) {
                if (!isset($result['weekly'][$dateStr]))
                    $result['weekly'][$dateStr] = array();
                if (!isset($result['weekly'][$dateStr][$k]))
                    $result['weekly'][$dateStr][$k] = 0;
                $result['weekly'][$dateStr][$k] += $v;
            }

//monthly
            $month = $date->format('M Y');
            foreach ($value as $k => $v) {
                if (!isset($result['monthly'][$month]))
                    $result['monthly'][$month] = array();
                if (!isset($result['weekly'][$month][$k]))
                    $result['monthly'][$month][$k] = 0;
                $result['monthly'][$month][$k] += $v;
            }
        }
        return $result;
    }

    public static function x_week_range($date) {
        $ts = strtotime($date);
        $start = strtotime('last monday', $ts);
        if (strtotime('this monday', $ts) == $ts)
            $start = strtotime('this monday', $ts);
        $end = strtotime('next sunday', $ts);
        if (strtotime('this sunday', $ts) == $ts)
            $end = strtotime('this sunday', $ts);
        return array(date('M d', $start),
            date('M d', $end));
    }

    public static function getGenderDistributionData($_filter) {
        return array(
            'gender' => 'male;female;undefined',
            'data' => '50%;30%;20%'
        );
    }

    public static function getTopLanguagesData($_filter) {
        return array(
            'language' => 'English;Other;Japanese;Spanish;Thai;Vietnamese',
            'data' => '30%;12%;9%;5%;4%;1%'
        );
    }

    public static function getGeographicDistributionData($_filter) {
        return array(
            'location' => '1;2;3;4',
            'data' => '30%;20%;10%;4%'
        );
    }

    public static function getExposureData($_filter) {
        return array(
            'daily' => '63000000;174000000;52000',
            'date' => 'Sep 10;Sep 11;Sep 12;Sep 13;Sep 14;Sep 15;Sep 16',
            'data' => '100000;150000;250000;168000;154000;240000;350000'
        );
    }

    public static function getConversationMapData($_filter) {
        return array(
            'keyword' => 'iphone 5;iphone 5s;apple;iPhone 5C;gold;finerfrint;phone;video',
            'data' => '38%;36%;7%;168000;154000;240000;350000'
        );
    }

    public static function getTopSources($_filter) {
        $streamId = $_filter["streamId"];
        $type = $_filter["type"];
        $stream = Stream::withId($streamId);
        $query = $stream->query;
        ZConfig::$zPort = "3004";
        $spamQuery = SpamMessages::getQuery(base64_decode($_filter['streamId']));
        if ($type)
            $source = new ZSourceReader($_filter["dateFrom"], $_filter["dateTo"], 7, $query . $spamQuery, $_filter["language"], $type);
        else
            $source = new ZSourceReader($_filter["dateFrom"], $_filter["dateTo"], 7, $query . $spamQuery, $_filter["language"]);
        try {
//print_r($source->read());
            $sources = $source->read();
            $data .= "<table id=\"tableTopSources\">"
                    . "<thead><th width=\"5%\">Rank</th>"
                    . "<th width=\"55%\">Source</th>"
                    . "<th width=\"15%\">Posts</th>"
                    . "<th width=\"15%\">Authors</th>"
                    . "<th width=\"15%\">Threads</th></thead><tbody>";
            $index = 1;
            foreach ($sources->getItems() as $src) {
                $data .= "<tr>"
                        . "<td align=\"center\">" . $index++ . "</td>"
                        . "<td><a href=\"" . $src->getSource() . "\" target=\"_blank\">" . $src->getSource() . "</a></td>"
                        . "<td align=\"center\">" . $src->getNumberOfPosts() . "</td>"
                        . "<td align=\"center\">" . $src->getNumberOfAuthors() . "</td>"
                        . "<td align=\"center\" style=\"font-size:inherit\">" . $src->getNumberOfThreads() . "</td>"
                        . "</tr>";
            }
            $data .= "</tbody></table>";
            ZConfig::$zPort = "3003";
            return $data;
        } catch (Exception $e) {
            ZConfig::$zPort = "3003";
            return $e->getMessage();
        }
    }

    public static function getSentimentByPlatforms($_filter) {
        $rows = array();
        $rows['tagname'] = array();
        $streamId = base64_decode($_filter["streamId"]);
        $db = Database::getInstance();
        $sentiment = '';
        $tagJoin = '';
        $sentimentFilter = 0;
        if (isset($_filter['sentiment']))
            $sentimentFilter = $_filter['sentiment'];
        if ($sentimentFilter != 0)
            $sentiment = "AND sentimentdata.score = " . (2 - $sentimentFilter);
        $tag = '';
        $tagFilter = array('0');
        if (isset($_filter['tags'])) {
            $tagFilter = $_filter['tags'];
        }
        if ($tagFilter != array('0')) {
            $tags = implode(',', $_filter['tags']);
            $tagJoin = 'LEFT JOIN signals ON sentimentdata.messageid = signals.messageid AND sentimentdata.streamid = signals.streamid LEFT JOIN tagsdata ON tagsdata.signalid = signals.signalid LEFT JOIN tags ON tags.tagid = tagsdata.tagid';
            $tag = "AND tags.tagid IN ($tags)";
        }
        $date = '';
        if ($_filter['dateFrom'] && $_filter['dateTo']) {
            $dateFrom = new DateTime($_filter['dateFrom']);
            $dateTo = new DateTime($_filter['dateTo']);
            $date = 'AND sentimentdata.datecreated >= "' . $dateFrom->format('Y-m-d') . ' 00:00:00" AND sentimentdata.datecreated <= "' . $dateTo->format('Y-m-d') . ' 23:59:59"';
        }
        $typeSQL = 'AND sentimentdata.type > 0';
        if ($_filter['type'] != array('0')) {
            $types = implode(',', $_filter['type']);
            $typeSQL = 'AND sentimentdata.type IN (' . $types . ')';
        }
        $sourceSQL = '';
        if ($_filter['source'] != array('0')) {
            $source = $_filter['source'];
            $sources = implode('\', \'', $source);
            $sourceSQL = " AND sentimentdata.source IN ('$sources')";
        }
        $query = "SELECT score, count(score) AS count, sentimentdata.type FROM sentimentdata $tagJoin WHERE sentimentdata.streamid = $streamId $sentiment $tag $date $typeSQL $sourceSQL GROUP BY sentimentdata.type, score";
        $result = $db->query($query);
        $rows = array();
        if ($result->returnCode == 200) {
            if ($result->dataTable->numOfRow > 0) {
                foreach ($result->dataTable->rows as $row) {
                    $type = ucfirst(Metrics::getSMTName($row['type']));
                    $rows[$type][$row['score']] = 0 + $row['count'];
                    if (!isset($rows[$type]['total']))
                        $rows[$type]['total'] = 0;
                    if ($row['score'] !== '-3')
                        $rows[$type]['total'] += $row['count'];
                    $rows[$type]['id'] = 0 + $row['type'];
                    if (!isset($rows[$type]['-1']))
                        $rows[$type]['-1'] = 0;
                    if (!isset($rows[$type]['0']))
                        $rows[$type]['0'] = 0;
                    if (!isset($rows[$type]['1']))
                        $rows[$type]['1'] = 0;
                }
                return $rows;
            }
            return $rows;
        }
        return $rows;
    }

    public static function getSentimentBySources($_filter) {
        $rows = array();
        $rows['tagname'] = array();
        $streamId = base64_decode($_filter["streamId"]);
        $db = Database::getInstance();
        $sentimentFilter = 0;
        if (isset($_filter['sentiment']))
            $sentimentFilter = $_filter['sentiment'];
        $sentiment = '';
        if ($sentimentFilter != 0)
            $sentiment = "AND sentimentdata.score = " . (2 - $sentimentFilter);
        $tag = '';
        $tagJoin = '';
        if ($_filter['tags'] != array('0')) {
            $tags = implode(',', $_filter['tags']);
            $tag = "AND tags.tagid IN ($tags)";
            $tagJoin = ' LEFT JOIN signals ON sentimentdata.messageid = signals.messageid AND sentimentdata.streamid = signals.streamid LEFT JOIN tagsdata ON tagsdata.signalid = signals.signalid LEFT JOIN tags ON tags.tagid = tagsdata.tagid';
        }
        $date = '';
        if ($_filter['dateFrom'] && $_filter['dateTo']) {
            $dateFrom = new DateTime($_filter['dateFrom']);
            $dateTo = new DateTime($_filter['dateTo']);
            $date = 'AND sentimentdata.datecreated >= "' . $dateFrom->format('Y-m-d') . '" AND sentimentdata.datecreated <= "' . $dateTo->format('Y-m-d') . '"';
        }
        $typeSQL = 'AND sentimentdata.type > 0';
        if ($_filter['type'] != array('0')) {
            $types = implode(',', $_filter['type']);
            $typeSQL = 'AND sentimentdata.type IN (' . $types . ')';
        }
        $sourceSQL = '';
        if ($_filter['source'] != array('0')) {
            $source = $_filter['source'];
            $sources = implode('\', \'', $source);
            $sourceSQL = " AND sentimentdata.source IN ('$sources')";
        }
        $query = "SELECT score, count(score) AS count, sentimentdata.source FROM sentimentdata $tagJoin WHERE sentimentdata.streamid = $streamId $sentiment $tag $date $typeSQL $sourceSQL GROUP BY sentimentdata.source, score";
        $result = $db->query($query);
        $rows = array();
        if ($result->returnCode == 200) {
            if ($result->dataTable->numOfRow > 0) {
                foreach ($result->dataTable->rows as $row) {
                    $source = $row['source'];
                    $rows[$source][$row['score']] = 0 + $row['count'];
                    if (!isset($rows[$source]['total']))
                        $rows[$source]['total'] = 0;
                    if ($row['score'] !== '-3')
                        $rows[$source]['total'] += $row['count'];
                    if (!isset($rows[$source]['-1']))
                        $rows[$source]['-1'] = 0;
                    if (!isset($rows[$source]['0']))
                        $rows[$source]['0'] = 0;
                    if (!isset($rows[$source]['1']))
                        $rows[$source]['1'] = 0;
                }
                return $rows;
            }
            return $rows;
        }
        return $rows;
    }

    public static function getChartPNG($chartOptions, $width, $height) {
        $ch = curl_init();

        curl_setopt($ch, CURLOPT_URL, "http://export.highcharts.com/");
        curl_setopt($ch, CURLOPT_POST, 1);
        $chartOptionsString = urlencode($chartOptions);
        curl_setopt($ch, CURLOPT_POSTFIELDS, "options=$chartOptionsString&type=image/png&async=true&width=$width");

        // in real life you should use something like:
        // curl_setopt($ch, CURLOPT_POSTFIELDS, 
        //          http_build_query(array('postvar1' => 'value1')));
        // receive server response ...
        curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);

        $server_output = curl_exec($ch);

        curl_close($ch);

        // further processing ....
        echo ($server_output);

        $ch = curl_init();

        curl_setopt($ch, CURLOPT_URL, "http://export.highcharts.com/" . $server_output);
        // in real life you should use something like:
        // curl_setopt($ch, CURLOPT_POSTFIELDS, 
        //          http_build_query(array('postvar1' => 'value1')));
        // receive server response ...
        curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);

        $server_output = curl_exec($ch);
        curl_close($ch);
        return base64_encode($server_output);
    }

    public static function generateSentimentAggregatesChart($_filter) {
        $data = self::getSentimentData($_filter);
        $chartData = array();
        foreach ($data['daily'] as $values) {
            $chartData[] = $values[-1];
            $chartData[] = $values[0];
            $chartData[] = $values[1];
        }
        var_dump($chartData);
        $chartData0 = $chartData[1];
        $chartData1 = $chartData[2];
        $chartData_1 = $chartData[0];
        return "{
            chart: {
                plotBackgroundColor: null,
                plotBorderWidth: 0,
                plotShadow: false
            },
            title: {
                text: ''
            },
            tooltip: {
                pointFormat: '<b>{point.percentage:.1f}%</b>'
            },
            plotOptions: {
                pie: {
                    size: 300,
                    dataLabels: {
                        enabled: true,
                        color: '#000000',
                        connectorColor: '#000000',
                        format: '<b>{point.name}</b>: {point.percentage:.1f} %'
                    },
                    center: ['50%', '50%']
                }
            },
            credits: {
                enabled: false
            },
            legends: {
                enabled: false,
            },
            series: [{
                    type: 'pie',
                    name: '',
                    innerSize: '40%',
                    data: [
                        {name: 'Positive',
                            y: $chartData1,
                            color: '#4f81bd',
                            fillOpacity: 1
                        },
                        {name: 'Neutral',
                            y: $chartData0,
                            color: '#9bbb59',
                            fillOpacity: 1
                        },
                        {name: 'Negative',
                            y: $chartData_1,
                            color: '#c0504d',
                            fillOpacity: 1
                        }
                    ]
                }]
        }";
    }
    
    public static function generateFunctionalSentimentChart($_filter) {
        $data = Tag::getMetric($_filter);
        var_dump($data);
        
        $chartData = array();
        foreach ($data['daily'] as $values) {
            $chartData[] = $values[-1];
            $chartData[] = $values[0];
            $chartData[] = $values[1];
        }
        var_dump($chartData);
        $chartData0 = $chartData[1];
        $chartData1 = $chartData[2];
        $chartData_1 = $chartData[0];
        return "{
            chart: {
                plotBackgroundColor: null,
                plotBorderWidth: 0,
                plotShadow: false
            },
            title: {
                text: ''
            },
            tooltip: {
                pointFormat: '<b>{point.percentage:.1f}%</b>'
            },
            plotOptions: {
                pie: {
                    size: 300,
                    dataLabels: {
                        enabled: true,
                        color: '#000000',
                        connectorColor: '#000000',
                        format: '<b>{point.name}</b>: {point.percentage:.1f} %'
                    },
                    center: ['50%', '50%']
                }
            },
            credits: {
                enabled: false
            },
            legends: {
                enabled: false,
            },
            series: [{
                    type: 'pie',
                    name: '',
                    innerSize: '40%',
                    data: [
                        {name: 'Positive',
                            y: $chartData1,
                            color: '#4f81bd',
                            fillOpacity: 1
                        },
                        {name: 'Neutral',
                            y: $chartData0,
                            color: '#9bbb59',
                            fillOpacity: 1
                        },
                        {name: 'Negative',
                            y: $chartData_1,
                            color: '#c0504d',
                            fillOpacity: 1
                        }
                    ]
                }]
        }";
    }

}
